"""
Case Type   : fullsql
Case Name   : 校验enable_stmt_track参数是否生效
Create At   : 2022/01/30
Owner       : zhao-pengcheng-g
Description :
    1.检查参数，设置enable_stmt_track为on
    2.log_min_duration_statement参数设置为-1时，创建表，查询statement_history表记录
    3.log_min_duration_statement参数设置为0时，删除表，查询statement_history表记录
    4.设置enable_stmt_track=off
    5.log_min_duration_statement参数设置为0时，创建表，查询statement_history表记录
    6.环境清理
Expect      :
    1.设置成功
    2.设置成功,创建表成功,查询statement_history表无记录
    3.设置成功,删除表成功,查询statement_history表有记录
    4..设置成功
    5.设置成功,创建表成功,查询statement_history表无记录
    6.环境清理成功
History     :
"""

import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Case0024 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.rootNode = Node('PrimaryRoot')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_name = 't_fullsql_0024'

    def test_fullsql(self):
        text = '---step1:检查参数，设置enable_stmt_track为on,expect:设置成功---'
        self.logger.info(text)
        self.config_enable = 'enable_stmt_track'
        self.show_enable_cmd = f'show {self.config_enable};'
        check_enable = self.sh_primy.execut_db_sql(self.show_enable_cmd)
        self.logger.info(check_enable)
        self.enable_default = check_enable.splitlines()[-2].strip()
        if 'on' != self.enable_default:
            guc_enable = self.sh_primy.execute_gsguc('reload',
                                                     self.constant.GSGUC_SUCCESS_MSG,
                                                     f'{self.config_enable}=on')
            self.assertTrue(guc_enable, '执行失败:' + text)
        check_enable_msg = self.sh_primy.execut_db_sql(self.show_enable_cmd)
        self.logger.info(check_enable_msg)
        self.common.equal_sql_mdg(check_enable_msg, self.config_enable, 'on',
                                  '(1 row)', flag='1')

        text = "---step2:log_min_duration_statement参数设置为-1时，创建表，" \
               "查询statement_history表记录,expect:设置成功,创建表," \
               "查询statement_history表无记录---"
        self.logger.info(text)
        sql_cmd = f"select reset_unique_sql('global', 'all', 1);" \
                  f"set log_min_duration_statement=-1;" \
                  f"drop table if exists {self.table_name};" \
                  f"create table {self.table_name}( a int,b int);" \
                  f"select pg_sleep(100);"
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn('reset_unique_sql' and '(1 row)',
                      sql_msg, '执行失败:' + text)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS,
                      sql_msg, '执行失败:' + text)
        select_cmd = f"select * from statement_history " \
                     f"where query like '%{self.table_name}%';"
        select_msg = self.sh_primy.execut_db_sql(select_cmd, dbname='postgres')
        self.logger.info(select_msg)
        self.assertIn('(0 rows)', select_msg, '执行失败:' + text)

        text = "---step3:log_min_duration_statement参数设置为0时，删除表，" \
               "查询statement_history表记录,expect:设置成功,创建表," \
               "查询statement_history表有记录---"
        self.logger.info(text)
        sql_cmd = f"set log_min_duration_statement=0;" \
                  f"drop table {self.table_name};" \
                  f"select pg_sleep(100);"
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS,
                      sql_msg, '执行失败:' + text)
        select_cmd = f"select *  from statement_history where query " \
                     f"like '%drop table {self.table_name}%';"
        select_msg = self.sh_primy.execut_db_sql(select_cmd, dbname='postgres')
        self.logger.info(select_msg)
        self.assertIn('(1 row)', select_msg, '执行失败:' + text)

        text = f'---step4:设置enable_stmt_track=off,expect:设置成功---'
        self.logger.info(text)
        guc_off = self.sh_primy.execute_gsguc('reload',
                                              self.constant.GSGUC_SUCCESS_MSG,
                                              f'{self.config_enable}=off')
        self.assertTrue(guc_off, '执行失败:' + text)
        show_enable_msg = self.sh_primy.execut_db_sql(self.show_enable_cmd)
        self.logger.info(show_enable_msg)
        self.common.equal_sql_mdg(show_enable_msg, self.config_enable, 'off',
                                  '(1 row)', flag='1')

        text = "---step5:log_min_duration_statement参数设置为0时，删除表，" \
               "查询statement_history表记录,expect:设置成功,创建表," \
               "查询statement_history表无记录---"
        self.logger.info(text)
        sql_cmd = f"set log_min_duration_statement=0;" \
                  f"drop table if exists {self.table_name};" \
                  f"create table {self.table_name}( a int,b int);" \
                  f"select pg_sleep(100);"
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS,
                      sql_msg, '执行失败:' + text)
        select_cmd = f"select * from statement_history " \
                     f"where query like '%create table {self.table_name}%';"
        select_msg = self.sh_primy.execut_db_sql(select_cmd, dbname='postgres')
        self.logger.info(select_msg)
        self.assertIn('(0 rows)', select_msg, '执行失败:' + text)

    def tearDown(self):
        text = '---step6:环境清理，expect:成功---'
        self.logger.info(text)
        text = '---检查参数，恢复原值:enable_stmt_track---'
        self.logger.info(text)
        check_enable = self.sh_primy.execut_db_sql(self.show_enable_cmd)
        self.logger.info(check_enable)
        current = check_enable.splitlines()[-2].strip()
        if self.enable_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_enable}={self.enable_default}')
            self.assertTrue(result, '参数恢复失败')
        show_enable_msg = self.sh_primy.execut_db_sql(self.show_enable_cmd)
        self.logger.info(show_enable_msg)
        drop_cmd = f'drop table {self.table_name};'
        drop_msg = self.sh_primy.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      drop_msg, '执行失败:' + text)
        self.common.equal_sql_mdg(show_enable_msg, self.config_enable,
                                  self.enable_default, '(1 row)', flag='1')
        self.logger.info(
            '---Opengauss_Function_Fullsql_Case0024 finish---')
